package com.multicampus.minishop.services.user.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import com.multicampus.minishop.common.SearchVO;
import com.multicampus.minishop.user.vo.UserVO;
import com.multicampus.minishop.util.DBUtil;

public class UserDAO {
	public void insertUser(UserVO vo) throws Exception {
		Connection con = DBUtil.getConnection();
		String sql = "insert into USERS values(?, ?, ?, 'user', ?, ?, ?, ?, sysdate)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, vo.getUserId());
		pstmt.setString(2, vo.getUserName());
		pstmt.setString(3, vo.getPassword());
		pstmt.setString(4, vo.getSsn());
		pstmt.setString(5, vo.getPhone());
		pstmt.setString(6, vo.getAddr());
		pstmt.setString(7, vo.getEmail());
		
		pstmt.executeUpdate();
		con.close();
	}
//	public UserVO selectUser(String userId) throws Exception {
//		return null;
//	}
//	
//	@SuppressWarnings("unchecked")
//	public HashMap getUserList(SearchVO searchVo) throws Exception {
//		return null;
//	}
//	public void updateUser(UserVO vo) throws Exception {
//	}

	
	public UserVO findUser(String userId) throws Exception {
		try {
			Connection con = DBUtil.getConnection();

			String sql = "select * from USERS where USER_ID=?";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setString(1, userId);

			ResultSet rs = stmt.executeQuery();

			UserVO vo = null;
			while (rs.next()) {

				vo = new UserVO();
				vo.setUserId(rs.getString("USER_ID"));
				vo.setUserName(rs.getString("USER_NAME"));
				vo.setPassword(rs.getString("PASSWORD"));
				vo.setRole(rs.getString("ROLE"));
				vo.setSsn(rs.getString("SSN"));
				vo.setPhone(rs.getString("CELL_PHONE"));
				vo.setAddr(rs.getString("ADDR"));
				vo.setEmail(rs.getString("EMAIL"));
				vo.setRegDate(rs.getDate("REG_DATE"));
			}

			con.close();

			return vo;
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}

	}

	public HashMap getUserList(SearchVO searchVO) throws Exception {
		HashMap map = null;
		try {
			Connection con = DBUtil.getConnection();

			String sql = "select * from USERS ";
			if (searchVO.getSearchCondition() != null) {
				if (searchVO.getSearchCondition().equals("0")) {
					sql += " where USER_ID='" + searchVO.getSearchKeyword()
							+ "'";
				} else if (searchVO.getSearchCondition().equals("1")) {
					sql += " where USER_NAME='" + searchVO.getSearchKeyword()
							+ "'";
				}
			}
			sql += " order by USER_ID";

			PreparedStatement stmt = con.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			ResultSet rs = stmt.executeQuery();

			rs.last();
			int total = rs.getRow();

			map = new HashMap();
			map.put("count", new Integer(total));



			rs.absolute(searchVO.getPage() * searchVO.getPageUnit() - 2);

			ArrayList list = new ArrayList();
			if (total > 0) {
				for (int i = 0; i < searchVO.getPageUnit(); i++) {

					UserVO vo = new UserVO();
					vo.setUserId(rs.getString("USER_ID"));
					vo.setUserName(rs.getString("USER_NAME"));
					vo.setPassword(rs.getString("PASSWORD"));
					vo.setRole(rs.getString("ROLE"));
					vo.setSsn(rs.getString("SSN"));
					vo.setPhone(rs.getString("CELL_PHONE"));
					vo.setAddr(rs.getString("ADDR"));
					vo.setEmail(rs.getString("EMAIL"));
					vo.setRegDate(rs.getDate("REG_DATE"));

					list.add(vo);
					if (!rs.next())
						break;
				}
			}
			map.put("list", list);

			con.close();

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		return map;
	}

	public void updateUser(UserVO vo) throws Exception {
		try {
			Connection con = DBUtil.getConnection();

			String sql = "update USERS set USER_NAME=?,CELL_PHONE=?,ADDR=?,EMAIL=? where USER_ID=?";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setString(1, vo.getUserName());
			stmt.setString(2, vo.getPhone());
			stmt.setString(3, vo.getAddr());
			stmt.setString(4, vo.getEmail());
			stmt.setString(5, vo.getUserId());
			
			stmt.executeUpdate();

			con.close();

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} 
	}


	public void deleteUser(String userId) throws Exception {
		try {
			Connection con = DBUtil.getConnection();

			String sql = "delete from USERS where USER_ID=? ";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setString(1, userId);
			stmt.executeUpdate();

			con.close();

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} 
	}
}
